package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.google.gson.Gson;



public class Utilities {
	
	public static boolean validateAddress(String streetLine1, String city, String country){
		boolean isAddressValid = true;
			if(streetLine1==null||streetLine1.trim().isEmpty()) isAddressValid = false;
			if(city==null||city.trim().isEmpty()) isAddressValid = false;
			if(country==null||country.trim().isEmpty()) isAddressValid = false;
		return isAddressValid;
	}
	
	public static boolean validateRecipient(String name, String phone){
		boolean isRecipientValid = true;
		if(name == null || name.trim().isEmpty()){
			return false;
		}
		if(phone == null || phone.trim().isEmpty()){
			return false;
		}
		
		return isRecipientValid;
	}
	
	public static Map<Long,String> getColors() throws  DatabaseException{
		
		Map<Long,String> colors = new HashMap<Long, String>();
		String colorQuery = "Select * from Colors";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(colorQuery);){
				rs = statement.executeQuery();
			
				while(rs!=null && rs.next()){
					colors.put(rs.getLong("id"), rs.getString("name"));
			
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return Collections.unmodifiableMap(colors);
	}
	
	public static Map<Long,String> getSizes() throws  DatabaseException{
		
		Map<Long, String> sizes = new HashMap<Long,String>();
		String sizeQuery = "Select * from Sizes";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(sizeQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				sizes.put(rs.getLong("id"), rs.getString("name"));
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		
		return Collections.unmodifiableMap(sizes);
	}
	
	public static Iterator<Material> getMaterials() throws  DatabaseException{
		Material material = null;
		List<Material> materials = new ArrayList<Material>();
		String materialQuery = "Select * from Materials";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(materialQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				material = new Material(rs.getInt("id"),rs.getString("description"),0);
				materials.add(material);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return materials.iterator();
	}
	
	public static Iterator<Brand> getBrands() throws  DatabaseException{
		Brand brand = null;
		List<Brand> brands = new ArrayList<Brand>();
		String brandQuery = "Select * from Brands";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(brandQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				brand = new Brand(rs.getInt("id"),rs.getString("description"));
				brands.add(brand);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return brands.iterator();
	}
	
	public static Iterator<Producer> getProducers() throws  DatabaseException{
		Producer producer = null;
		List<Producer> producers = new ArrayList<Producer>();
		String producerQuery = "Select * from Producers";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(producerQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				producer = new Producer(rs.getInt("id"),rs.getString("description"));
				producers.add(producer);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return producers.iterator();
	}
	
	public static Iterator<CategoryBean> getCategories() throws  DatabaseException{
		CategoryBean category = null;
		List<CategoryBean> categories = new ArrayList<CategoryBean>();
		String categoryQuery = "Select * from Categories";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(categoryQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				category = new CategoryBean(rs.getInt("id"),rs.getString("name"));
				categories.add(category);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return categories.iterator();
	}
	
	
	public static Map<Integer, String> getIdToCategoryDescriptionMap() throws  DatabaseException{
		Map<Integer,String> idToCategoryMap = new HashMap<Integer, String>();
		String categoryQuery = "Select * from Categories";
		ResultSet rs = null;
		int id;
		String name;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(categoryQuery);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				id=rs.getInt("id");
				name = rs.getString("name");
				idToCategoryMap.put(id, name);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return idToCategoryMap;
	}
	
	public static Set<Integer> getProductCategoryIds(String id) throws DatabaseException{
		Set<Integer> productCategoryIds = new HashSet<Integer>();
		String query = "Select * from item_categories where item_id = " +id;
		ResultSet rs = null;
		int categoryId;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(query);){
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				categoryId = rs.getInt("category_id");
				productCategoryIds.add(categoryId);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return productCategoryIds;
		
	}
	public static Iterator<Present> getPresents(long supplier_id) throws  DatabaseException{
		Present present = null;
		List<Present> presents = new ArrayList<Present>();
		String presentQuery = "Select id,description,figure_path from Presents where count>0 and supplier_id = ?";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(presentQuery);){
				statement.setLong(1, supplier_id);
				rs = statement.executeQuery();
			while(rs!=null && rs.next()){
				present = new Present(rs.getInt("id"),rs.getString("description"),rs.getString("figure_path"));
				presents.add(present);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return presents.iterator();
	}
	
	public static long getUniqueNumber() throws  DatabaseException{	
		String uniqueNumberQuery = "SELECT unique_number_seq.nextval FROM dual";
		ResultSet rs = null;
		long uniqueNumber =0;
		try(Connection con = ConnectionPool.getConnection(); 
				PreparedStatement statement = con.prepareStatement(uniqueNumberQuery);){
				rs = statement.executeQuery();
			if (rs!=null && rs.next()){
				uniqueNumber = rs.getLong(1);
			}
			
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			}catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return uniqueNumber;
	}
	
	public static void insertColors(String[] colorNames, String[] colorCodes) throws  DatabaseException{
		
		String colorQuery = "insert into Colors(name,code) values (?,?)";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(colorQuery);){
				con.setAutoCommit(false);
				String colorName;
				String colorCode;
				for(int i=0;i<colorNames.length; i++){
					colorName = colorNames[i];
					colorCode = colorCodes[i];
					statement.setString(1, colorName);
					statement.setString(2, colorCode);
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}
	
	public static void insertSizes(String[] sizes) throws  DatabaseException{
		
		String sizeQuery = "insert into Sizes(name) values (?)";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(sizeQuery);){
				con.setAutoCommit(false);
				String size;
				for(int i=0;i<sizes.length; i++){
					size = sizes[i];
					statement.setString(1, size);
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}
	
	public static void insertBrands(String[] brands) throws  DatabaseException{
		
		String brandsQuery = "insert into Brands(description) values (?)";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(brandsQuery);){
				con.setAutoCommit(false);
				String brand;
				for(int i=0;i<brands.length; i++){
					brand = brands[i];
					statement.setString(1, brand);
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}
	
public static void insertMaterials(String[] materials) throws  DatabaseException{
		
		String  materialsQuery = "insert into Materials(description) values (?)";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(materialsQuery);){
				con.setAutoCommit(false);
				String material;
				for(int i=0;i<materials.length; i++){
					material = materials[i];
					statement.setString(1, material);
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}

public static void insertCategories(String[] categories) throws  DatabaseException{
	
	String  categoriesQuery = "insert into Categories(name) values (?)";
	Connection con = null;
	try{
		con = ConnectionPool.getConnection();
		
		try(PreparedStatement statement = con.prepareStatement(categoriesQuery);){
			con.setAutoCommit(false);
			String category;
			for(int i=0;i<categories.length; i++){
				category = categories[i];
				statement.setString(1, category);
				statement.executeUpdate();
			}
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				throw new DatabaseException(e);
			}
			throw new DatabaseException(e);
		}
	}finally{
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
	}

}

	public static void updatePrices(String[] ids, String[] prices, String[] promotionalPrices ) throws  DatabaseException{
		
		String  updatePricesQuery = "Update Items SET price=?, promotional_price=? where id=? ";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(updatePricesQuery);){
				con.setAutoCommit(false);
				String id;
				String promotionalPrice;
				String price;
				for(int i=0;i<ids.length; i++){
					id = ids[i];
					price = prices[i];
					promotionalPrice = promotionalPrices[i];
					statement.setDouble(1, Double.parseDouble(price));
					if(promotionalPrice.isEmpty() == false){
						statement.setDouble(2, Double.parseDouble(promotionalPrice));
					}else{
						statement.setNull(2, Types.DOUBLE);
					}
					statement.setLong(3, Long.parseLong(id));
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}
	

	public static void updateCounts(String[] inventoryIds, String[] counts) throws  DatabaseException{
		
		String  updateCountsQuery = "Update Clothing_Inventory SET count=?  where inventory_id=? ";
		Connection con = null;
		try{
			con = ConnectionPool.getConnection();
			
			try(PreparedStatement statement = con.prepareStatement(updateCountsQuery);){
				con.setAutoCommit(false);
				String inventoryId;
				String count;
	
				for(int i=0;i<inventoryIds.length; i++){
					inventoryId = inventoryIds[i];
//					System.out.println("Inventory id equals"+inventoryId);
					count = counts[i];
					statement.setInt(1, Integer.parseInt(count));
					statement.setLong(2, Long.parseLong(inventoryId));
					statement.executeUpdate();
				}
				con.commit();
			} catch (SQLException e) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					throw new DatabaseException(e);
				}
				throw new DatabaseException(e);
			}
		}finally{
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
	
	}
	
	private static void deleteFromTable(String table, String identifierName, String identifierValue, Connection con) throws SQLException{
		String query = "delete from "+table+ " where " + identifierName + "=" +identifierValue;
		ResultSet rs = null;
		try (PreparedStatement preparedStatement = con.prepareStatement(query);) {
			preparedStatement.executeUpdate();
		}
	}

	public static void deleteProduct(String id) throws DatabaseException {
		
		Connection con=null;
		try {
			con = ConnectionPool.getConnection();
			con.setAutoCommit(false);
			deleteFromTable("item_categories", "item_id", id, con);
			deleteFromTable("item_materials", "item_id", id, con);
			deleteFromTable("clothing_inventory","item_id",  id, con);
			deleteFromTable("features","item_id",  id, con);
			deleteFromTable("item_presents","item_id",  id, con);
			deleteFromTable("figures", "item_id", id, con);
			deleteFromTable("additional_sizes", "item_id", id, con);
			deleteFromTable("items", "id", id, con);
			con.commit();
		} catch (SQLException e) {
			try {
				if(con!=null){
					con.rollback();
				}
			} catch (SQLException e1) {

				throw new DatabaseException(e1);
			}
			throw new DatabaseException(e);
		}finally{
			try {
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		
		
	}
	

	public static void categorize(int productId, int[] removeFromCategories, int[] addToCategories) throws DatabaseException {
		
		Connection con=null;
		try {
			con = ConnectionPool.getConnection();
			con.setAutoCommit(false);
			removeFromCategories(productId, removeFromCategories, con);
			addToCategories(productId, addToCategories, con);
						con.commit();
		} catch (SQLException e) {
			try {
				if(con!=null){
					con.rollback();
				}
			} catch (SQLException e1) {

				throw new DatabaseException(e1);
			}
			throw new DatabaseException(e);
		}finally{
			try {
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		
		
	}
	
	private static void addToCategories(int productId,
			int[] categories, Connection con) throws SQLException {
		if(categories==null) return;
		String query = "insert into item_categories(item_id, category_id) values (?,?)";
		try (PreparedStatement preparedStatement = con.prepareStatement(query);) {
			for(int category:categories){
				preparedStatement.setLong(1, productId);
				preparedStatement.setLong(2, category);
				preparedStatement.executeUpdate();
			}
		}
		
	}

	private static void removeFromCategories(int productId,
			int[] categories, Connection con) throws SQLException {
		if(categories==null) return;
		String query = "delete from item_categories where item_id=? and category_id=?";
		try (PreparedStatement preparedStatement = con.prepareStatement(query);) {
			for(int category:categories){
				preparedStatement.setLong(1, productId);
				preparedStatement.setLong(2, category);
				preparedStatement.executeUpdate();
			}
		}
		
	}

	public static int[] convertToIntegers(String[] array){
		if(array==null) return null;
		int[] integers = new int[array.length];
		for (int i=0;i<array.length;i++){
			integers[i] = Integer.parseInt(array[i]);
		}
		return integers;
	}
	
	public static int getCount(long inventoryId) throws DatabaseException {
		int count = -1;
		String query = "select count from Clothing_Inventory where inventory_id = ?";
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection();
			PreparedStatement statement = con.prepareStatement(query);	
				){
			statement.setLong(1, inventoryId);
			rs = statement.executeQuery();
			if (rs!=null && rs.next()){
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DatabaseException(e);
		} finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new DatabaseException(e);
				}
			}
		}
		
		return count;
		
	}
	
	
//	public static void main(String[] args) throws DatabaseException {
//		Gson gson = new Gson();
//		Iterator<Color> colors = getColors();
//		while(colors.hasNext()){
//			System.out.println(colors.next());
//		}
//		Iterator<Size> sizes = getSizes();
//		while(sizes.hasNext()){
//			System.out.println(sizes.next());
//		}
		
//		Iterator<Material> materials = getMaterials();
//		while(materials.hasNext()){
//			System.out.println(materials.next());
//		}
		
//		Iterator<Brand> brands = getBrands();
//		while(brands.hasNext()){
//			System.out.println(brands.next());
//		}
		
//		Iterator<Brand> brands = getBrands();
//		while(brands.hasNext()){
//			System.out.println(brands.next());
//		}
//		Iterator<Producer> producers = getProducers();
//		while(producers.hasNext()){
//			System.out.println(producers.next());
//		}
		
//		Iterator<CategoryBean> categories = getCategories();
//		while(categories.hasNext()){
//			System.out.println(categories.next());
//		}
//		Iterator<Present> presents = getPresents();
//		while(presents.hasNext()){
//			System.out.println(presents.next());
//		}
//		System.out.println(getUniqueNumber());
//		System.out.println(getCount(135));
//	}

	
}
